﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Odds.Entity;
using System.Data.SqlClient;
using Odds.DBUtility;
using System.Collections;
using System.Data;

namespace Odds.DataAccess
{
    public class OddsCompanyController
    {
        private const string SQL_GETCOMPANYIDSET = "SELECT CompanyID, CompanyName FROM OddsCompany WHERE CompanyName IN";
        //private string SQL_INSERT = string.Format("INSERT INTO OddsCompany({0}) VALUES(@{0})",PARM_COMPANYNAME);
        private string SQL_GETALLUNDELETEDODDSCOMPANY = "SELECT CompanyID, CompanyName FROM OddsCompany WHERE " + SQL_CONDITION_UNDELETED;
        private const string SQL_CONDITION_UNDELETED = " IsDeleted = 0 ";

        private const string PARM_COMPANYNAME = "CompanyName";

        //public void Insert(List<OddsCompany> oddsCompanySet)
        //{
        //    Dictionary<string,int> oddsCompanySetInDatabase = GetOddsCompanyDictionary();

        //    foreach (OddsCompany oddsCompany in oddsCompanySet)
        //    {
        //        if (oddsCompanySetInDatabase.ContainsKey(oddsCompany.CompanyName))
        //        {
        //            SqlParameter[] parm = { new SqlParameter("@" + PARM_COMPANYNAME, oddsCompany.CompanyName) };
        //            OddsSQLHelper.ExecuteNonQuery(CommandType.Text, SQL_INSERT, parm);
        //        }
        //    }
        //}

        public Dictionary<string, Guid> Insert(List<OddsCompany> oddsCompanySet)
        {
            Dictionary<string, Guid> result = new Dictionary<string,Guid>();
            try
            {
                StringBuilder sql = new StringBuilder();
                string companyNameList = string.Join(@"',N'", oddsCompanySet.Select(o => o.CompanyName).ToArray());

                foreach (OddsCompany oddsCompany in oddsCompanySet)
                {
                    Guid guid = Guid.NewGuid();
                    sql.AppendFormat("EXEC Pro_DictionaryInsert 'OddsCompany','CompanyID','CompanyName',N'{0}',N'{1}';",
                        guid, oddsCompany.CompanyName);
                }
                sql.Append(@"SELECT CompanyName, CompanyID FROM OddsCompany WHERE CompanyName IN (N'" +
                    companyNameList + @"') AND " + SQL_CONDITION_UNDELETED);

                result = OddsSQLHelper.GetStringKeyDictionary(sql.ToString());
            }
            catch (Exception e)
            {
                throw e;
            }
            
            return result;
        }

        public Dictionary<string, Guid> GetOddsCompanyDictionary()
        {
            Dictionary<string, Guid> result = new Dictionary<string, Guid>();
            SqlDataReader dr = OddsSQLHelper.ExecuteReader(CommandType.Text, SQL_GETALLUNDELETEDODDSCOMPANY, null);
            while (dr.Read())
            {
                result.Add(dr.GetString(1),dr.GetGuid(0));
            }
            return result;
        }

    }
}
